"""
数据库工具类 以及 数据模型
使用sqlalchemy
"""
from enum import unique
import sqlite3
from sqlalchemy import create_engine, Column, Integer, String, Boolean, DateTime, Text, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from datetime import datetime
import os
import sys
from pathlib import Path

# 添加src目录到Python路径
src_path = Path(__file__).parent.parent / 'src'
sys.path.insert(0, str(src_path))

from utils.logger import _get_logger

logger = _get_logger()

Base = declarative_base()


class Contact(Base):
    """联系人表 - 存储唯一URL，其他字段留空"""
    __tablename__ = 'contacts'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    url = Column(String(500), unique=True, nullable=False, comment='联系人URL')
    url_phone = Column(String(50), comment='电话号码（留空）')
    has_whatsapp = Column(String(10), comment='是否有WhatsApp（留空）')
    remarks = Column(Text, comment='备注（留空）')
    email = Column(String(200), comment='邮箱地址（留空）')
    social_media = Column(String(500), comment='社交媒体（留空）')
    contact_remarks = Column(Text, comment='联系备注（留空）')
    other_remarks = Column(Text, comment='其他备注（留空）')
    updated = Column(DateTime, default=datetime.now, onupdate=datetime.now, comment='更新时间')
    
    # 与Company表的关系
    companies = relationship("Company", back_populates="contact", cascade="all, delete-orphan")

    def __repr__(self):
        return f"<Contact(id={self.id}, url='{self.url}')>"


class Company(Base):
    """公司表 - 存储所有excel读取的详细数据"""
    __tablename__ = 'company'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    contacts_id = Column(Integer, ForeignKey('contacts.id'), nullable=False, comment='关联的联系人ID')
    map_coordinate = Column(String(200), comment='地图坐标')
    company_name = Column(String(200), comment='公司名称')
    industry = Column(String(100), comment='行业')
    phone = Column(String(50), comment='电话号码')
    address = Column(Text, comment='地址')
    country = Column(String(100), comment='国家')
    created_at = Column(DateTime, default=datetime.utcnow, comment='创建时间')
    
    # 与Contact表的关系
    contact = relationship("Contact", back_populates="companies")
    
    def __repr__(self):
        return f"<Company(id={self.id}, company_name='{self.company_name}', contacts_id={self.contacts_id})>"


class DatabaseHelper:
    """数据库工具类"""
    
    def __init__(self, db_path='../data/uae_contacts.db'):
        self.db_path = db_path
        self.engine = None
        self.Session = None
        self._init_database()
    
    def _init_database(self):
        """初始化数据库连接和表结构"""
        try:
            # 创建数据库引擎
            self.engine = create_engine(f'sqlite:///{self.db_path}', echo=False)
            
            # 创建所有表
            Base.metadata.create_all(self.engine)
            
            # 创建会话工厂
            self.Session = sessionmaker(bind=self.engine)
            
            logger.info(f"数据库初始化成功: {self.db_path}")
            
        except Exception as e:
            logger.error(f"❌ 数据库初始化失败: {e}")
            raise
    
    def get_session(self):
        """获取数据库会话"""
        return self.Session()
    
    def insert_contacts(self, contacts_data):
        """
        批量插入联系人数据到双表结构
        - contacts表：只存储url（唯一），其他字段留空
        - company表：存储所有excel读取的详细数据
        
        Args:
            contacts_data: 联系人数据列表，每个元素是包含联系人信息的字典
            
        Returns:
            dict: 包含详细统计信息的字典
                - contact_inserted: Contact表新增记录数
                - contact_updated: Contact表更新记录数（实际为0，因为只存储url）
                - company_inserted: Company表新增记录数
        """
        if not contacts_data:
            logger.warning("没有数据需要插入")
            return {'contact_inserted': 0, 'contact_updated': 0, 'company_inserted': 0}
        
        session = self.get_session()
        contact_inserted = 0
        contact_updated = 0
        company_inserted = 0
        
        try:
            for data in contacts_data:
                # 处理URL
                url = data.get('url')
                if not url or not isinstance(url, str) or url.strip() == '':
                    logger.warning(f"跳过无效URL的数据: {data}")
                    continue
                
                url = url.strip()
                
                # 检查contacts表中是否已存在该URL
                existing_contact = session.query(Contact).filter_by(url=url).first()
                
                if not existing_contact:
                    # URL不存在，创建新的contact记录（只存储url，其他字段留空）
                    new_contact = Contact(url=url)
                    session.add(new_contact)
                    session.flush()  # 获取新插入记录的ID
                    contact_id = new_contact.id
                    contact_inserted += 1
                    logger.debug(f"插入新联系人: URL={url}, ID={contact_id}")
                else:
                    # URL已存在，使用现有contact的ID
                    contact_id = existing_contact.id
                    logger.debug(f"使用现有联系人: URL={url}, ID={contact_id}")
                
                # 创建company记录，存储所有excel数据
                new_company = Company(
                    contacts_id=contact_id,
                    map_coordinate=data.get('map_coordinate'),
                    company_name=data.get('company_name'),
                    industry=data.get('industry'),
                    phone=data.get('phone'),
                    address=data.get('address'),
                    country=data.get('country')
                )
                session.add(new_company)
                company_inserted += 1
                logger.debug(f"插入公司信息: Contact ID={contact_id}, Company={data.get('company_name')}")
            
            logger.info(f"数据处理完成: Contact表新增{contact_inserted}条；Company表新增{company_inserted}条")
            
            # 提交事务
            session.commit()
            logger.info("数据库事务提交成功")
            
            return {
                'contact_inserted': contact_inserted,
                'contact_updated': contact_updated,
                'company_inserted': company_inserted
            }
            
        except Exception as e:
            session.rollback()
            logger.error(f"❌ 插入联系人数据失败: {e}")
            raise
        finally:
            session.close()
    
    def _compare_contact_data(self, existing_contact, new_data):
        """对比联系人数据是否有变化（仅比较Contact表字段）"""
        # Contact表需要对比的字段列表
        compare_fields = [
            'phone', 'country', 'has_whatsapp', 'remarks',
            'email', 'social_media', 'contact_remarks', 'other_remarks'
        ]
        
        for field in compare_fields:
            existing_value = getattr(existing_contact, field)
            new_value = new_data.get(field)
            
            # 处理None值和空字符串的比较
            existing_value = existing_value if existing_value is not None else ''
            new_value = new_value if new_value is not None else ''
            
            if str(existing_value).strip() != str(new_value).strip():
                return True
        
        return False
    
    def get_contacts_count(self):
        """获取联系人总数"""
        session = self.get_session()
        try:
            count = session.query(Contact).count()
            return count
        except Exception as e:
            logger.error(f"❌ 获取联系人数量失败: {e}")
            return 0
        finally:
            session.close()
    
    def get_companies_count(self):
        """获取公司表中的记录总数"""
        session = self.Session()
        try:
            count = session.query(Company).count()
            logger.debug(f"公司表记录总数: {count}")
            return count
        except Exception as e:
            logger.error(f"获取公司记录数时出错: {e}")
            return 0
        finally:
            session.close()
    
    def get_total_counts(self):
        """获取所有表的记录数量"""
        session = self.get_session()
        try:
            contact_count = session.query(Contact).count()
            company_count = session.query(Company).count()
            return {
                'contacts': contact_count,
                'companies': company_count,
                'total': contact_count + company_count
            }
        except Exception as e:
            logger.error(f"❌ 获取数据统计失败: {e}")
            return {'contacts': 0, 'companies': 0, 'total': 0}
        finally:
            session.close()
    
    def clear_all_contacts(self):
        """清空所有联系人和公司数据（先删除Company表数据，再删除Contact表数据）"""
        session = self.Session()
        try:
            # 先删除Company表数据（因为有外键约束）
            company_count = session.query(Company).count()
            session.query(Company).delete()
            
            # 再删除Contact表数据
            contact_count = session.query(Contact).count()
            session.query(Contact).delete()
            
            session.commit()
            logger.info(f"成功清空数据库：删除{contact_count}条联系人记录，{company_count}条公司记录")
            return {'contacts_deleted': contact_count, 'companies_deleted': company_count}
            
        except Exception as e:
            session.rollback()
            logger.error(f"清空数据库时出错: {e}")
            raise
        finally:
            session.close()
    
    def close(self):
        """关闭数据库连接"""
        if self.engine:
            self.engine.dispose()
            logger.info("数据库连接已关闭")